home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Software Vault: The Gold Collection
/
Software Vault - The Gold Collection (American Databankers) (1993).ISO
/
cdr11
/
pdox693.zip
/
TI1311.ASC
< prev
next >
Wrap
Text File
|
1993-03-18
|
6KB
|
199 lines
PRODUCT : Paradox NUMBER : 1311
VERSION : All
OS : DOS
DATE : March 18, 1993 PAGE : 1/3
TITLE : Using a Changeto Query to Transfer Data From One
Table to Another
Intended Audience:
This technical information sheet is intended for Paradox users of
all levels.
Purpose:
The purpose of this technical information sheet is to explain the
process of performing a Changeto Query to copy data from one
table to another.
There are a number of ways to move or copy records from one table
to another. Some examples are:
- Tools | More | Add | Update. Modifies the target table by
updating existing records or adding new records to it.
This is the easiest technique; however, it requires that
the table structures be identical.
- Insert queries - Lets you add new records to a table.
- Changeto queries - To change specific fields in a table.
- PAL scripts
This document discusses how to use a Changeto query to copy data
from selected non-linking fields in one table to another table.
Changeto queries are useful in situations where the table
structures are different, or you do not want to update all the
fields in a table.
To perform a Changeto query there must be at least one linking
field between the tables (a field of the same type), as well as
matching field values between the linking field(s) in the tables.
If there is no matching field value for a given record, Paradox
cannot establish a link between the records and the copy will not
be performed. If there are no link values between the tables, an
Insert query can be performed to add data from one table to
another.
Suppose you want to update the Last Name field in the Orders
table from the Employee table. The first step would be to
restructure the Orders table and add a Last Name field to it. Do
PRODUCT : Paradox NUMBER : 1311
VERSION : All
OS : DOS
DATE : March 18, 1993 PAGE : 2/3
TITLE : Using a Changeto Query to Transfer Data From One
Table to Another
this by choosing Modify | Restructure from the Main menu. Bring
up a query form for each table by choosing Ask from the Main
menu.
EMPLOYEE╦═══Emp #════╦═══Last Name════╦═══Init═══╦══Position══╗
║ ║ ║ ║ ║
║ ║ ║ ║ ║
ORDERS╦══Cust #═══╦═══Date════╦═══Item═══╦══Emp#══╦══Last Name══╗
║ ║ ║ ║ ║ ║
║ ║ ║ ║ ║ ║
To build the query, link the tables on the common field, Emp #.
Link them by placing an example element in the Emp # field of
both query forms. Place an example element by pressing F5 and
then typing in an expression. The example element can be almost
anything, as long as it is the same in both two query forms. By
placing the same example element in both query forms, you have
informed Paradox that these fields are linking fields.
Next, place an example element in the Last Name field of the
source (Employee) table. This example element can be anything
except for the example element which was used in the linking
fields (in this example, the Emp# field). After placing this
example element, move to the Last Name field in the destination
table (in this example, Orders). Type the word CHANGETO, a
space, and then the same example element which was placed in the
Last Name field of the Employee query form. At this point, the
tables are linked, the second example element has been used to
represent the contents of the Last Name field in the source
table, and the CHANGETO expression has been typed into the Last
Name field in the destination table.
The query forms should look like this.
EMPLOYEE╦═══Emp #════╦═══Last Name════╦═══Init═══╦══Position══╗
║ _A ║ _B ║ ║ ║
║ ║ ║ ║ ║
ORDERS╦══Cust #═══╦═══Date════╦═══Item═══╦══Emp#══╦══Last Name══╗
║ ║ ║ ║ _A ║ CHANGETO _B ║
║ ║ ║ ║ ║ ║
PRODUCT : Paradox NUMBER : 1311
VERSION : All
OS : DOS
DATE : March 18, 1993 PAGE : 3/3
TITLE : Using a Changeto Query to Transfer Data From One
Table to Another
Note: The underscore character "_" in the query forms on the
previous page, represents an example element.
The query is now ready to process. To process the query, press
F2.
When the query is finished processing, a CHANGED table will
appear on screen. This table contains the records of the
destination table in the state that they were in before the query
was processed. Paradox provides this table as a backup. It is a
temporary table and it will be deleted when you exit Paradox, or
overwritten if you perform another CHANGETO query. By viewing
the table, you will be able to now see the updated records which
correspond to the records in the CHANGED table.
For additional information on Changeto and Insert queries, refer
to the Chapter 6 of the User's Guide (for versions 3.5 and
earlier, refer to Chapter 4 of the User's Guide).
DISCLAIMER: You have the right to use this technical information
subject to the terms of the No-Nonsense License Statement that
you received with the Borland product to which this information
pertains.